package hhs.poc.activities.whoiswho;

import java.util.Date;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseManager {
	Context context;
	private SQLiteDatabase db; // a reference to the database manager class.
	private final String DB_NAME = "db_content"; // the name of our database
	private int DB_VERSION = 1; // the version of the database

	private final String TABLE_ROW_ID = "id";

	// Employee
	private final String TABLE_NAME_EMP = "Employee";
	private final String EMP_COL_NAME = "name";
	private final String EMP_COL_JOB = "job";
	private final String EMP_COL_EMAIL = "email";
	private final String EMP_COL_ACADEMY = "academy";
	private final String EMP_COL_PHONE = "phone";
	private final String EMP_COL_ROOM = "room";
	private final String EMP_COL_PRESENT = "present";

	// Appointment
	private final String TABLE_NAME_APP = "Appointment";
	private final String APP_COL_EMP = "emp_id";
	private final String APP_COL_BEGIN = "begin";
	private final String APP_COL_END = "end";
	private final String APP_COL_DATE = "date";
	private final String APP_COL_LESSON = "lesson";
	private final String APP_COL_GROUP = "student_group";
	private final String APP_COL_ROOM = "room";

	// AppointmentEmployees
	private final String TABLE_NAME_EPP = "app_emp";
	private final String EPP_COL_APP = "app_id";
	private final String EPP_COL_EMP = "emp_id";

	public DatabaseManager(Context context) {
		this.context = context;
		CustomSQLiteOpenHelper helper = new CustomSQLiteOpenHelper(context);
		this.db = helper.getWritableDatabase();
	}

	public void addRow(String table_name, ContentValues values) {
		Log.println(Log.ASSERT, "table_name", table_name);
		Log.println(Log.ASSERT, "values", values.toString());
		try {
			db.insert(table_name, null, values);
		} catch (Exception e) {
			Log.println(Log.ASSERT, "DB ERROR", e.toString());
			e.printStackTrace();
		}
	}

	public void deleteRow(String table_name, String pk, long row_id) {
		try {
			db.delete(table_name, pk + "=" + row_id, null);
		} catch (Exception e) {
			Log.println(Log.ASSERT, "DB ERROR", e.toString());
			e.printStackTrace();
		}
	}

	public void updateRow(String table_name, String pk, long row_id,
			ContentValues values) {
		try {
			db.update(table_name, values, pk + "=" + row_id, null);
		} catch (Exception e) {
			Log.println(Log.ASSERT, "DB ERROR", e.toString());
			e.printStackTrace();
		}
	}

	public ArrayList<Object> getAllObjects(String table_name, String[] columns,
			long row_id, Settable object) {
		ArrayList<Object> objects = new ArrayList<Object>();
		Cursor cursor;

		try {
			cursor = db
					.query(table_name, columns, null, null, null, null, null);

			cursor.moveToFirst();

			if (!cursor.isAfterLast()) {
				do {
					object.setAll(cursor);
					objects.add(object);
				} while (cursor.moveToNext());
			}
		} catch (SQLException e) {
			Log.println(Log.ASSERT, "DB ERROR", e.toString());
			e.printStackTrace();
		}

		return objects;
	}

	public Employee getEmployee(int id) {
		Employee employee = new Employee();
		Cursor cursor;

		try {
			cursor = db.query(TABLE_NAME_EMP, new String[] { TABLE_ROW_ID,
					EMP_COL_NAME, EMP_COL_JOB, EMP_COL_EMAIL, EMP_COL_ACADEMY,
					EMP_COL_PHONE, EMP_COL_ROOM, EMP_COL_PRESENT },
					TABLE_ROW_ID + "=" + id, null, null, null, null, null);

			cursor.moveToFirst();

			if (!cursor.isAfterLast()) {
				do {
					employee.setAll(cursor);
				} while (cursor.moveToNext());
			}

			cursor.close();
		} catch (SQLException e) {
			Log.println(Log.ASSERT, "DB ERROR", e.toString());
			e.printStackTrace();
		}

		return employee;
	}

	public ArrayList<Employee> getEmployeesByRoom(String room_nr) {
		ArrayList<Employee> employees = new ArrayList<Employee>();
		Cursor cursor;

		try {
			cursor = db.query(TABLE_NAME_EMP, new String[] { TABLE_ROW_ID,
					EMP_COL_NAME, EMP_COL_JOB, EMP_COL_EMAIL, EMP_COL_ACADEMY,
					EMP_COL_PHONE, EMP_COL_ROOM, EMP_COL_PRESENT },
					EMP_COL_ROOM + " = '" + room_nr + "'", null, null, null,
					null, null);

			cursor.moveToFirst();

			if (!cursor.isAfterLast()) {
				do {
					Employee emp = new Employee();
					emp.setAll(cursor);
					employees.add(emp);
				} while (cursor.moveToNext());
			}

			cursor.close();
		} catch (SQLException e) {
			Log.println(Log.ASSERT, "DB ERROR", e.toString());
			e.printStackTrace();
		}

		return employees;
	}

	public ArrayList<Appointment> getAppointmentsByRoom(String room_nr,
			String date) {
		ArrayList<Appointment> appointments = new ArrayList<Appointment>();
		Cursor cursor;

		try {
			cursor = db.query(TABLE_NAME_APP, new String[] { TABLE_ROW_ID,
					APP_COL_EMP, APP_COL_DATE, APP_COL_LESSON,
					"strftime('%H:%M', " + APP_COL_BEGIN + ")",
					"strftime('%H:%M', " + APP_COL_END + ")", APP_COL_GROUP,
					APP_COL_ROOM }, APP_COL_ROOM + " = '" + room_nr + "' AND "
					+ APP_COL_DATE + " = '" + date + "'", null, null, null,
					APP_COL_BEGIN);

			cursor.moveToFirst();

			if (!cursor.isAfterLast()) {
				do {
					Appointment app = new Appointment();
					app.setAll(cursor);
					appointments.add(app);
				} while (cursor.moveToNext());
			}

			cursor.close();
		} catch (SQLException e) {
			Log.println(Log.ASSERT, "DB ERROR", e.toString());
			e.printStackTrace();
		}

		return appointments;
	}

	public ArrayList<Appointment> getAppointmentsByEmp(int emp_id, String date) {
		ArrayList<Appointment> appointments = new ArrayList<Appointment>();
		Cursor cursor;

		try {
			cursor = db.query(TABLE_NAME_APP, new String[] { TABLE_ROW_ID,
					APP_COL_EMP, APP_COL_DATE, APP_COL_LESSON,
					"strftime('%H:%M', " + APP_COL_BEGIN + ")",
					"strftime('%H:%M', " + APP_COL_END + ")", APP_COL_GROUP,
					APP_COL_ROOM }, APP_COL_EMP + " = '" + emp_id + "' AND "
					+ APP_COL_DATE + " = '" + date + "'", null, null, null,
					APP_COL_BEGIN);

			cursor.moveToFirst();

			if (!cursor.isAfterLast()) {
				do {
					Appointment app = new Appointment();
					app.setAll(cursor);
					appointments.add(app);
				} while (cursor.moveToNext());
			}

			cursor.close();
		} catch (SQLException e) {
			Log.println(Log.ASSERT, "DB ERROR", e.toString());
			e.printStackTrace();
		}

		return appointments;
	}

	public String[] getAppEmps(int id) {
		String[] emp_names = null;
		Cursor cursor;
		int i = 0;

		try {
			cursor = db.query(TABLE_NAME_EPP, new String[] { TABLE_ROW_ID,
					EPP_COL_APP, EPP_COL_EMP },
					EPP_COL_APP + " = '" + id + "'", null, null, null, null);

			emp_names = new String[cursor.getCount()];
			cursor.moveToFirst();

			if (!cursor.isAfterLast()) {
				do {
					Employee emp = getEmployee(cursor.getInt(2));
					emp_names[i] = emp.getName();
					i += 1;
				} while (cursor.moveToNext());
			}

			cursor.close();
		} catch (SQLException e) {
			Log.println(Log.ASSERT, "DB ERROR", e.toString());
			e.printStackTrace();
		}

		return emp_names;
	}

	private class CustomSQLiteOpenHelper extends SQLiteOpenHelper {

		public CustomSQLiteOpenHelper(Context context) {
			super(context, DB_NAME, null, DB_VERSION);
			context.deleteDatabase(DB_NAME);
		}

		public void createDatabase(SQLiteDatabase db) {
			final String create_emp = "create table " + TABLE_NAME_EMP + " ("
					+ TABLE_ROW_ID
					+ " integer primary key autoincrement not null,"
					+ EMP_COL_NAME + " text," + EMP_COL_JOB + " text,"
					+ EMP_COL_EMAIL + " text," + EMP_COL_ACADEMY + " text,"
					+ EMP_COL_PHONE + " text," + EMP_COL_ROOM + " text,"
					+ EMP_COL_PRESENT + " text);";

			final String create_app = "create table " + TABLE_NAME_APP + " ("
					+ TABLE_ROW_ID
					+ " integer primary key autoincrement not null,"
					+ APP_COL_EMP + " integer," + APP_COL_DATE + " text,"
					+ APP_COL_BEGIN + " date," + APP_COL_END + " date,"
					+ APP_COL_LESSON + " text," + APP_COL_GROUP + " text,"
					+ APP_COL_ROOM + " text);";

			final String create_app_emp = "create table " + TABLE_NAME_EPP
					+ " (" + TABLE_ROW_ID
					+ " integer primary key autoincrement not null,"
					+ EPP_COL_APP + " integer," + EPP_COL_EMP + " integer);";

			SimpleDateFormat date_format = new SimpleDateFormat("dd-MM-yyy");
			String date = date_format.format(new Date());

			// Employee 1
			ContentValues emp1 = new ContentValues();
			emp1.put(TABLE_ROW_ID, 1);
			emp1.put(EMP_COL_NAME, "Zor, O.");
			emp1.put(EMP_COL_JOB, "Docent");
			emp1.put(EMP_COL_EMAIL, "O.Zor@hhs.nl");
			emp1.put(EMP_COL_ACADEMY, "Academie voor ICT & Media");
			emp1.put(EMP_COL_PHONE, "+31 (0)70 445 7965");
			emp1.put(EMP_COL_ROOM, "SL 8.33");
			emp1.put(
					EMP_COL_PRESENT,
					"true true false true true false true true false true true false true true false");

			ContentValues emp1_app1 = new ContentValues();
			emp1_app1.put(TABLE_ROW_ID, 1);
			emp1_app1.put(APP_COL_EMP, 1);
			emp1_app1.put(APP_COL_DATE, date);
			emp1_app1.put(APP_COL_BEGIN, "1970-01-01 10:00:00");
			emp1_app1.put(APP_COL_END, "1970-01-01 12:00:00");
			emp1_app1.put(APP_COL_LESSON, "I-7 PHP");
			emp1_app1.put(APP_COL_GROUP, "I-7vt-p2.01");
			emp1_app1.put(APP_COL_ROOM, "SL 4.34");

			ContentValues emp1_app2 = new ContentValues();
			emp1_app2.put(TABLE_ROW_ID, 2);
			emp1_app2.put(APP_COL_EMP, 1);
			emp1_app2.put(APP_COL_DATE, date);
			emp1_app2.put(APP_COL_BEGIN, "1970-01-01 13:00:00");
			emp1_app2.put(APP_COL_END, "1970-01-01 14:00:00");
			emp1_app2.put(APP_COL_LESSON, "I-7 PHP");
			emp1_app2.put(APP_COL_GROUP, "I-7vt-p2.02");
			emp1_app2.put(APP_COL_ROOM, "SL 4.34");

			ContentValues emp1_app3 = new ContentValues();
			emp1_app3.put(TABLE_ROW_ID, 3);
			emp1_app3.put(APP_COL_EMP, 1);
			emp1_app3.put(APP_COL_DATE, date);
			emp1_app3.put(APP_COL_BEGIN, "1970-01-01 14:00:00");
			emp1_app3.put(APP_COL_END, "1970-01-01 15:00:00");
			emp1_app3.put(APP_COL_LESSON, "I-7 PHP");
			emp1_app3.put(APP_COL_GROUP, "I-7vt-p2.03");
			emp1_app3.put(APP_COL_ROOM, "SL 4.34");

//			ContentValues emp1_app4 = new ContentValues();
//			emp1_app4.put(TABLE_ROW_ID, 4);
//			emp1_app4.put(APP_COL_EMP, 1);
//			emp1_app4.put(APP_COL_DATE, date);
//			emp1_app4.put(APP_COL_BEGIN, "1970-01-01 15:00:00");
//			emp1_app4.put(APP_COL_END, "1970-01-01 16:00:00");
//			emp1_app4.put(APP_COL_LESSON, "I-7 PHP");
//			emp1_app4.put(APP_COL_GROUP, "I-7vt-p2.04");
//			emp1_app4.put(APP_COL_ROOM, "SL 4.34");
			
			ContentValues app1_emp1 = new ContentValues();
			app1_emp1.put(EPP_COL_APP, 1);
			app1_emp1.put(EPP_COL_EMP, 1);
			
			ContentValues app2_emp1 = new ContentValues();
			app2_emp1.put(EPP_COL_APP, 2);
			app2_emp1.put(EPP_COL_EMP, 1);
			
			ContentValues app3_emp1 = new ContentValues();
			app3_emp1.put(EPP_COL_APP, 3);
			app3_emp1.put(EPP_COL_EMP, 1);
			
//			ContentValues app4_emp1 = new ContentValues();
//			app4_emp1.put(EPP_COL_APP, 4);
//			app4_emp1.put(EPP_COL_EMP, 1);

			// Employee 2
			ContentValues emp2 = new ContentValues();
			emp2.put(TABLE_ROW_ID, 2);
			emp2.put(EMP_COL_NAME, "Vogels, M.J.M.G.");
			emp2.put(EMP_COL_JOB, "Hogeschooldocent");
			emp2.put(EMP_COL_EMAIL, "M.J.M.G.Vogels@hhs.nl");
			emp2.put(EMP_COL_ACADEMY, "Academie voor ICT & Media");
			emp2.put(EMP_COL_PHONE, "+31 (0)70 445 8503");
			emp2.put(EMP_COL_ROOM, "SL 8.33");
			emp2.put(
					EMP_COL_PRESENT,
					"true false false true true false true true false true true false true true false");

			ContentValues emp2_app1 = new ContentValues();
			emp2_app1.put(TABLE_ROW_ID, 5);
			emp2_app1.put(APP_COL_EMP, 2);
			emp2_app1.put(APP_COL_DATE, date);
			emp2_app1.put(APP_COL_BEGIN, "1970-01-01 09:00:00");
			emp2_app1.put(APP_COL_END, "1970-01-01 11:00:00");
			emp2_app1.put(APP_COL_LESSON, "I-7 Kwalitatief onderzoek");
			emp2_app1.put(APP_COL_GROUP, "I-7vt-p2.01");
			emp2_app1.put(APP_COL_ROOM, "SL 4.54");

			ContentValues emp2_app2 = new ContentValues();
			emp2_app2.put(TABLE_ROW_ID, 6);
			emp2_app2.put(APP_COL_EMP, 2);
			emp2_app2.put(APP_COL_DATE, date);
			emp2_app2.put(APP_COL_BEGIN, "1970-01-01 12:00:00");
			emp2_app2.put(APP_COL_END, "1970-01-01 14:00:00");
			emp2_app2.put(APP_COL_LESSON, "I-7 Kwalitatief onderzoek");
			emp2_app2.put(APP_COL_GROUP, "I-7vt-p2.02");
			emp2_app2.put(APP_COL_ROOM, "SL 4.54");

			ContentValues emp2_app3 = new ContentValues();
			emp2_app3.put(TABLE_ROW_ID, 7);
			emp2_app3.put(APP_COL_EMP, 2);
			emp2_app3.put(APP_COL_DATE, date);
			emp2_app3.put(APP_COL_BEGIN, "1970-01-01 14:00:00");
			emp2_app3.put(APP_COL_END, "1970-01-01 16:00:00");
			emp2_app3.put(APP_COL_LESSON, "I-7 Kwalitatief onderzoek");
			emp2_app3.put(APP_COL_GROUP, "I-7vt-p2.03");
			emp2_app3.put(APP_COL_ROOM, "SL 4.54");

			ContentValues emp2_app4 = new ContentValues();
			emp2_app4.put(TABLE_ROW_ID, 8);
			emp2_app4.put(APP_COL_EMP, 2);
			emp2_app4.put(APP_COL_DATE, date);
			emp2_app4.put(APP_COL_BEGIN, "1970-01-01 16:00:00");
			emp2_app4.put(APP_COL_END, "1970-01-01 18:00:00");
			emp2_app4.put(APP_COL_LESSON, "I-7 Kwalitatief onderzoek");
			emp2_app4.put(APP_COL_GROUP, "I-7vt-p2.04");
			emp2_app4.put(APP_COL_ROOM, "SL 4.54");

			ContentValues app5_emp2 = new ContentValues();
			app5_emp2.put(EPP_COL_APP, 5);
			app5_emp2.put(EPP_COL_EMP, 2);
			
			ContentValues app6_emp2 = new ContentValues();
			app6_emp2.put(EPP_COL_APP, 6);
			app6_emp2.put(EPP_COL_EMP, 2);
			
			ContentValues app7_emp2 = new ContentValues();
			app7_emp2.put(EPP_COL_APP, 7);
			app7_emp2.put(EPP_COL_EMP, 2);
			
			ContentValues app8_emp2 = new ContentValues();
			app8_emp2.put(EPP_COL_APP, 8);
			app8_emp2.put(EPP_COL_EMP, 2);
			
			// Employee 3
			ContentValues emp3 = new ContentValues();
			emp3.put(TABLE_ROW_ID, 3);
			emp3.put(EMP_COL_NAME, "Maas, J.D.");
			emp3.put(EMP_COL_JOB, "Hogeschooldocent");
			emp3.put(EMP_COL_EMAIL, "J.D.Maas@hhs.nl");
			emp3.put(EMP_COL_ACADEMY, "Academie voor ICT & Media");
			emp3.put(EMP_COL_PHONE, "+31 (0)70 445 8511");
			emp3.put(EMP_COL_ROOM, "SL 8.33");
			emp3.put(
					EMP_COL_PRESENT,
					"true true false true true false true true false true true true false false false");

			ContentValues emp3_app1 = new ContentValues();
			emp3_app1.put(TABLE_ROW_ID, 9);
			emp3_app1.put(APP_COL_EMP, 3);
			emp3_app1.put(APP_COL_DATE, date);
			emp3_app1.put(APP_COL_BEGIN, "1970-01-01 09:00:00");
			emp3_app1.put(APP_COL_END, "1970-01-01 10:00:00");
			emp3_app1.put(APP_COL_LESSON, "I-7 Les");
			emp3_app1.put(APP_COL_GROUP, "I-7vt-p2.01");
			emp3_app1.put(APP_COL_ROOM, "SL 4.30");

			ContentValues emp3_app2 = new ContentValues();
			emp3_app2.put(TABLE_ROW_ID, 10);
			emp3_app2.put(APP_COL_EMP, 3);
			emp3_app2.put(APP_COL_DATE, date);
			emp3_app2.put(APP_COL_BEGIN, "1970-01-01 12:00:00");
			emp3_app2.put(APP_COL_END, "1970-01-01 13:00:00");
			emp3_app2.put(APP_COL_LESSON, "I-7 Les");
			emp3_app2.put(APP_COL_GROUP, "I-7vt-p2.02");
			emp3_app2.put(APP_COL_ROOM, "SL 4.30");

			ContentValues emp3_app3 = new ContentValues();
			emp3_app3.put(TABLE_ROW_ID, 11);
			emp3_app3.put(APP_COL_EMP, 3);
			emp3_app3.put(APP_COL_DATE, date);
			emp3_app3.put(APP_COL_BEGIN, "1970-01-01 13:00:00");
			emp3_app3.put(APP_COL_END, "1970-01-01 14:00:00");
			emp3_app3.put(APP_COL_LESSON, "I-7 Les");
			emp3_app3.put(APP_COL_GROUP, "I-7vt-p2.03");
			emp3_app3.put(APP_COL_ROOM, "SL 4.30");

			ContentValues emp3_app4 = new ContentValues();
			emp3_app4.put(TABLE_ROW_ID, 12);
			emp3_app4.put(APP_COL_EMP, 3);
			emp3_app4.put(APP_COL_DATE, date);
			emp3_app4.put(APP_COL_BEGIN, "1970-01-01 16:00:00");
			emp3_app4.put(APP_COL_END, "1970-01-01 17:00:00");
			emp3_app4.put(APP_COL_LESSON, "I-7 Les");
			emp3_app4.put(APP_COL_GROUP, "I-7vt-p2.04");
			emp3_app4.put(APP_COL_ROOM, "SL 4.30");

			ContentValues app9_emp3 = new ContentValues();
			app9_emp3.put(EPP_COL_APP, 9);
			app9_emp3.put(EPP_COL_EMP, 3);
			
			ContentValues app10_emp3 = new ContentValues();
			app10_emp3.put(EPP_COL_APP, 10);
			app10_emp3.put(EPP_COL_EMP, 3);
			
			ContentValues app11_emp3 = new ContentValues();
			app11_emp3.put(EPP_COL_APP, 11);
			app11_emp3.put(EPP_COL_EMP, 3);
			
			ContentValues app12_emp3 = new ContentValues();
			app12_emp3.put(EPP_COL_APP, 12);
			app12_emp3.put(EPP_COL_EMP, 3);
			
			// Employee 4
			ContentValues emp4 = new ContentValues();
			emp4.put(TABLE_ROW_ID, 4);
			emp4.put(EMP_COL_NAME, "Heijne, G.C.M.");
			emp4.put(EMP_COL_JOB, "Hogeschooldocent");
			emp4.put(EMP_COL_EMAIL, "G.C.M.Heijne@hhs.nl");
			emp4.put(EMP_COL_ACADEMY, "Academie voor ICT & Media");
			emp4.put(EMP_COL_PHONE, "+31 (0)70 445 8470");
			emp4.put(EMP_COL_ROOM, "SL 8.33");
			emp4.put(
					EMP_COL_PRESENT,
					"false false false false false false false false false false false false false false false");

			ContentValues emp4_app1 = new ContentValues();
			emp4_app1.put(TABLE_ROW_ID, 13);
			emp4_app1.put(APP_COL_EMP, 4);
			emp4_app1.put(APP_COL_DATE, date);
			emp4_app1.put(APP_COL_BEGIN, "1970-01-01 09:00:00");
			emp4_app1.put(APP_COL_END, "1970-01-01 11:00:00");
			emp4_app1.put(APP_COL_LESSON, "I-7 Databases");
			emp4_app1.put(APP_COL_GROUP, "I-7vt-p2.01");
			emp4_app1.put(APP_COL_ROOM, "SL 4.28");

			ContentValues emp4_app2 = new ContentValues();
			emp4_app2.put(TABLE_ROW_ID, 14);
			emp4_app2.put(APP_COL_EMP, 4);
			emp4_app2.put(APP_COL_DATE, date);
			emp4_app2.put(APP_COL_BEGIN, "1970-01-01 11:00:00");
			emp4_app2.put(APP_COL_END, "1970-01-01 13:00:00");
			emp4_app2.put(APP_COL_LESSON, "I-7 Databases");
			emp4_app2.put(APP_COL_GROUP, "I-7vt-p2.02");
			emp4_app2.put(APP_COL_ROOM, "SL 4.28");

			ContentValues emp4_app3 = new ContentValues();
			emp4_app3.put(TABLE_ROW_ID, 15);
			emp4_app3.put(APP_COL_EMP, 4);
			emp4_app3.put(APP_COL_DATE, date);
			emp4_app3.put(APP_COL_BEGIN, "1970-01-01 13:30:00");
			emp4_app3.put(APP_COL_END, "1970-01-01 15:30:00");
			emp4_app3.put(APP_COL_LESSON, "I-7 Databases");
			emp4_app3.put(APP_COL_GROUP, "I-7vt-p2.03");
			emp4_app3.put(APP_COL_ROOM, "SL 4.28");

			ContentValues emp4_app4 = new ContentValues();
			emp4_app4.put(TABLE_ROW_ID, 16);
			emp4_app4.put(APP_COL_EMP, 4);
			emp4_app4.put(APP_COL_DATE, date);
			emp4_app4.put(APP_COL_BEGIN, "1970-01-01 15:30:00");
			emp4_app4.put(APP_COL_END, "1970-01-01 17:30:00");
			emp4_app4.put(APP_COL_LESSON, "I-7 Databases");
			emp4_app4.put(APP_COL_GROUP, "I-7vt-p2.04");
			emp4_app4.put(APP_COL_ROOM, "SL 4.28");

			ContentValues app13_emp4 = new ContentValues();
			app13_emp4.put(EPP_COL_APP, 13);
			app13_emp4.put(EPP_COL_EMP, 4);
			
			ContentValues app14_emp4 = new ContentValues();
			app14_emp4.put(EPP_COL_APP, 14);
			app14_emp4.put(EPP_COL_EMP, 4);
			
			ContentValues app15_emp4 = new ContentValues();
			app15_emp4.put(EPP_COL_APP, 15);
			app15_emp4.put(EPP_COL_EMP, 4);
			
			ContentValues app16_emp4 = new ContentValues();
			app16_emp4.put(EPP_COL_APP, 16);
			app16_emp4.put(EPP_COL_EMP, 4);
			
			// Employee 5
			ContentValues emp5 = new ContentValues();
			emp5.put(TABLE_ROW_ID, 5);
			emp5.put(EMP_COL_NAME, "Reijnhoudt, M.");
			emp5.put(EMP_COL_JOB, "Hogeschooldocent");
			emp5.put(EMP_COL_EMAIL, "M.Reijnhoudt@hhs.nl");
			emp5.put(EMP_COL_ACADEMY, "Academie voor ICT & Media");
			emp5.put(EMP_COL_PHONE, "+31 (0)70 445 8473");
			emp5.put(EMP_COL_ROOM, "SL 7.21");
			emp5.put(
					EMP_COL_PRESENT,
					"false false false false false false false false false false false false false false false");

			ContentValues room1_app1 = new ContentValues();
			room1_app1.put(TABLE_ROW_ID, 17);
			room1_app1.put(APP_COL_EMP, 1);
			room1_app1.put(APP_COL_DATE, date);
			room1_app1.put(APP_COL_BEGIN, "1970-01-01 12:00:00");
			room1_app1.put(APP_COL_END, "1970-01-01 13:00:00");
			room1_app1.put(APP_COL_LESSON, "Vergadering");
			room1_app1.put(APP_COL_GROUP, "");
			room1_app1.put(APP_COL_ROOM, "SL 8.33");
			
			ContentValues app17_emp1 = new ContentValues();
			app17_emp1.put(EPP_COL_APP, 17);
			app17_emp1.put(EPP_COL_EMP, 1);
			
//			ContentValues app17_emp2 = new ContentValues();
//			app17_emp2.put(EPP_COL_APP, 17);
//			app17_emp2.put(EPP_COL_EMP, 2);
//			
//			ContentValues app17_emp3 = new ContentValues();
//			app17_emp3.put(EPP_COL_APP, 17);
//			app17_emp3.put(EPP_COL_EMP, 3);
//			
//			ContentValues app17_emp4 = new ContentValues();
//			app17_emp4.put(EPP_COL_APP, 17);
//			app17_emp4.put(EPP_COL_EMP, 4);
			
			ContentValues PAS_app1 = new ContentValues();
			PAS_app1.put(TABLE_ROW_ID, 18);
			PAS_app1.put(APP_COL_EMP, 1);
			PAS_app1.put(APP_COL_DATE, date);
			PAS_app1.put(APP_COL_BEGIN, "1970-01-01 15:30:00");
			PAS_app1.put(APP_COL_END, "1970-01-01 17:00:00");
			PAS_app1.put(APP_COL_LESSON, "Presentatie & Assessment (PAS)");
			PAS_app1.put(APP_COL_GROUP, "I-7vt-p2.07");
			PAS_app1.put(APP_COL_ROOM, "SL 7.53");

			ContentValues app18_emp1 = new ContentValues();
			app18_emp1.put(EPP_COL_APP, 18);
			app18_emp1.put(EPP_COL_EMP, 1);
			
			ContentValues app18_emp5 = new ContentValues();
			app18_emp5.put(EPP_COL_APP, 18);
			app18_emp5.put(EPP_COL_EMP, 5);

			try {
				// create tables
				db.execSQL(create_emp);
				db.execSQL(create_app);
				db.execSQL(create_app_emp);

				// inserts employees
				db.insert(TABLE_NAME_EMP, null, emp1);
				db.insert(TABLE_NAME_EMP, null, emp2);
				db.insert(TABLE_NAME_EMP, null, emp3);
				db.insert(TABLE_NAME_EMP, null, emp4);
				db.insert(TABLE_NAME_EMP, null, emp5);

				// insert appointment for employee 1
				db.insert(TABLE_NAME_APP, null, emp1_app1);
				db.insert(TABLE_NAME_APP, null, emp1_app2);
				db.insert(TABLE_NAME_APP, null, emp1_app3);
//				db.insert(TABLE_NAME_APP, null, emp1_app4);

				// insert appointment for employee 2
				db.insert(TABLE_NAME_APP, null, emp2_app1);
				db.insert(TABLE_NAME_APP, null, emp2_app2);
				db.insert(TABLE_NAME_APP, null, emp2_app3);
				db.insert(TABLE_NAME_APP, null, emp2_app4);

				// insert appointment for employee 3
				db.insert(TABLE_NAME_APP, null, emp3_app1);
				db.insert(TABLE_NAME_APP, null, emp3_app2);
				db.insert(TABLE_NAME_APP, null, emp3_app3);
				db.insert(TABLE_NAME_APP, null, emp3_app4);

				// insert appointment for employee 4
				db.insert(TABLE_NAME_APP, null, emp4_app1);
				db.insert(TABLE_NAME_APP, null, emp4_app2);
				db.insert(TABLE_NAME_APP, null, emp4_app3);
				db.insert(TABLE_NAME_APP, null, emp4_app4);

				// insert appointment for room 1
				db.insert(TABLE_NAME_APP, null, room1_app1);
				
				// insert assessment appointment
				db.insert(TABLE_NAME_APP, null, PAS_app1);
				
				// insert all app_emp rows
				db.insert(TABLE_NAME_EPP, null, app1_emp1);
				db.insert(TABLE_NAME_EPP, null, app2_emp1);
				db.insert(TABLE_NAME_EPP, null, app3_emp1);
//				db.insert(TABLE_NAME_EPP, null, app4_emp1);
				db.insert(TABLE_NAME_EPP, null, app5_emp2);
				db.insert(TABLE_NAME_EPP, null, app6_emp2);
				db.insert(TABLE_NAME_EPP, null, app7_emp2);
				db.insert(TABLE_NAME_EPP, null, app8_emp2);
				db.insert(TABLE_NAME_EPP, null, app9_emp3);
				db.insert(TABLE_NAME_EPP, null, app10_emp3);
				db.insert(TABLE_NAME_EPP, null, app11_emp3);
				db.insert(TABLE_NAME_EPP, null, app12_emp3);
				db.insert(TABLE_NAME_EPP, null, app13_emp4);
				db.insert(TABLE_NAME_EPP, null, app14_emp4);
				db.insert(TABLE_NAME_EPP, null, app15_emp4);
				db.insert(TABLE_NAME_EPP, null, app16_emp4);
				db.insert(TABLE_NAME_EPP, null, app17_emp1);
//				db.insert(TABLE_NAME_EPP, null, app17_emp2);
//				db.insert(TABLE_NAME_EPP, null, app17_emp3);
//				db.insert(TABLE_NAME_EPP, null, app17_emp4);
				db.insert(TABLE_NAME_EPP, null, app18_emp1);
				db.insert(TABLE_NAME_EPP, null, app18_emp5);

			} catch (Exception e) {
				Log.println(Log.ASSERT, "DB ERROR", e.toString());
				e.printStackTrace();
			}
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			createDatabase(db);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		}
	}
}
